#-*-coding:utf-8-*-
'''
Created on 2009/6/6

@author: chenhh
'''
import cStringIO
import string
import re
import sqlite3
from lxml import html
from lxml import etree
import fetchstockid
import time
import psyco

def parseTWSETable():
    psyco.full()
    stock_ids = fetchstockid.fetchTWSEStockID()
#    stock_ids=(2330,)
    
    startyear = 1990
    endyear = 2008
    startmonth = 1
    endmonth = 12
    numofdata = len(stock_ids)*(endyear-startyear+1)*(endmonth-startmonth+1)
    count = 0
    
    dbname = '../../twse_stock.s3db'
    dbconn = sqlite3.connect(dbname)
    dbcur = dbconn.cursor()
    t1 = time.time()
    
    for stockid in stock_ids:
        for year in xrange(startyear, endyear+1):
            for month in xrange(startmonth, endmonth+1):  
                formatedMon = string.zfill(month, 2)
                count += 1
                try:
                    filename = '/media/disk-1/stock_price/%s_%s%s.htm'%(stockid, year, formatedMon)
                    file = open(filename)
                    data = file.read()
                except:
                    print filename," not exist"
                    continue
                
                html = etree.HTML(data)
                trlist = html.xpath('//table[@class]/tr[@class="basic2"]');
                
                #insert into table transaction_record
                #columns(serial_number, stock_id, date, volume, total_cost, open_price, 
                #        highest_price, lowest_price, close_price)
                for idx,tr in enumerate(trlist):
                      str =""
                      #skip title line
                      if(idx == 0):
                          continue
                      for cnt, el in enumerate(tr) :
                          #parse date column
                          if(cnt == 0):
                              for date in el:
                                  str +="'"+ date.text +"' ,"
                          else:
                              str += deleteCommaInString(el.text) +','
                      
                      result = "'%s', %s"%(stockid, str[0:len(str)-1])
                      #只取前9項的資料
                      list = result.split(",")[0:8]
                      sql = 'insert into transaction_record values(%s)'%string.join(list, ',')
                      print "[%.3f]: %s"%(float(count)/numofdata*100, sql)
                      dbcur.execute(sql)
                      dbconn.commit()
                
    dbcur.close()
    dbconn.close()
    t2 = time.time()
    print "total time = %.3f secs"%(t2-t1)

def deleteCommaInString(str):
    return string.join(str.split(","), "")


if __name__ == '__main__':
    parseTWSETable()
     
     
